package cn.pengpeng.hiveanli2;

public class Sql {
public static void main(String[] args) {
	
}
/**
电商交易项目案例

Sdate定义了日期的分类，将每天分别赋予所属的月份、星期、季度等属性，
字段分别为日期、年月、年、月、日、周几、第几周、季度、旬、半月；

Stock定义了订单表头，字段分别为订单号、交易位置、交易日期；

StockDetail文件定义了订单明细，该表和Stock以交易号进行关联，
字段分别为订单号、行号、货品、数量、价格、金额；

CREATE TABLE sdate(
dateID STRING, --日期
theyearmonth STRING,--年月
theyear INT,--年
themonth INT,--月
thedate INT,--日
theweek INT,--周几
theweeks INT,--第几周
thequot INT,--季度
thetenday INT,--旬
thehalfmonth INT --半月
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' ;

CREATE TABLE stock(
ordernumber STRING,--订单号
locationid STRING,--交易位置
dateID STRING --交易日期
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' ;

CREATE TABLE stockdetail(
ordernumber STRING,--订单号
rownum INT,--行号
itemid STRING, --货品
qty INT, --数量
price INT, --价格
amount INT --总金额
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' ;

创建表，导入数据
LOAD DATA LOCAL INPATH '/usr/local/test3/sdate.txt'
OVERWRITE INTO TABLE sdate;

LOAD DATA LOCAL INPATH '/usr/local/test3/stock.txt'
OVERWRITE INTO TABLE stock;

LOAD DATA LOCAL INPATH '/usr/local/test3/stockdetail.txt'
OVERWRITE INTO TABLE stockdetail;

1、计算所有订单每年的总金额
	算法分析：
	要计算所有订单每年的总金额，首先需要获取所有订单的订单号、订单日期和订单金信息，
	然后把这些信息和日期表进行关联，
	获取年份信息，最后根据这四个列按年份归组统计获取所有订单每年的总金额。
关于三张表:stock a, stockdetail b, sdate c

SELECT c.theyear,SUM(b.amount)
  FROM stock a
  JOIN stockdetail b
    ON a.ordernumber = b.ordernumber
  JOIN sdate c
    ON a.dateid = c.dateid
 GROUP BY c.theyear 
 ORDER BY c.theyear;

Result:
2004	3265696
2005	13247234
2006	13670416
2007	16711974
2008	14670698
2009	6322137
2010	210924



2、计算所有订单 每年最大金额订单的销售额
	算法分析：
	
	该算法分为两步：	
	1.按照日期和订单号进行归组计算，
	  获取每个订单号每天的销售数据；
	stock a,stockdetail b

SELECT a.dataid,a.ordernumber,SUM(b.amount)
  FROM stock a
  JOIN stockdetail b 
    ON a.ordernumber = b.ordernumber
GROUP BY a.dataid,a.ordernumber;

	2.把第一步获取的数据和日期表进行关联获取的年份信息，
	然后按照年份进行归组，使用Max函数，获取所有订单每年最大金额订单的销售额。
sdate c,第一步获取的数据 d

SELECT c.theyear,MAX(d.sumofamount) 
  FROM sdate c
  JOIN (
SELECT a.dateid,a.ordernumber,SUM(b.amount) AS sumofamount
  FROM stock a
  JOIN stockdetail b 
    ON a.ordernumber = b.ordernumber
GROUP BY a.dateid,a.ordernumber) d
WHERE | on c.dateid=d.dateid
GROUP BY c.theyear 
ORDER BY c.theyear;


Result:
2004	23612
2005	38180
2006	36124
2007	159126
2008	55828
2009	25810
2010	13063

3、统计所有订单中季度销售额前10位
stock a,stockdetail b,sdate c

SELECT c.theyear,c.thequot,SUM(b.amount) AS sumofamount
  FROM stock a
  JOIN stockdetail b
    ON a.ordernumber = b.ordernumber
  JOIN sdate c
    ON a.dateid = c.dateid
GROUP BY c.theyear,c.thequot
ORDER BY sumofamount desc
LIMIT 10;

Result：
2008	1	5252819
2007	4	4613093
2007	1	4446088
2006	1	3916638
2008	2	3886470
2007	3	3870558
2007	2	3782235
2006	4	3691314
2005	1	3592007
2005	3	3304243
4、列出销售金额在100000以上的单据(订单号)
stock a,stockdetail b

SELECT ordernumber,SUM(amount) AS sumofamount
  FROM stockdetail
GROUP BY ordernumber
HAVING sumofamount > 100000;

Result:
HMJSL00009024	119058
HMJSL00009958	159126



 */
}
